﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KelionesLapaiV2
{
    class DataHelper
    {
        public static bool InsertUpdateData(SqlCeConnection conn, Data data)
        {
            string UpdateQuery = "UPDATE [Data] SET [kmStart]=" + data.KmStart + ", [kmEnd]=" + data.KmEnd +
                                 ", [kmDriven]=" + data.KmDriven + ", [ltUsed]=" + data.LtUsed + ", [ltStart]=" +
                                 data.LtStart + ", [ltFilled]=" + data.LtFilled + ", [ltEnd]=" + data.LtEnd + ", [notWorked]= '" + data.NotWorked +
                                 "' WHERE [Date] = '" + data.Date.ToShortDateString() + "'";
            string InsertQuery =
                "INSERT INTO [Data] ([Date], [kmStart], [kmEnd], [kmDriven], [ltUsed], [ltStart], [ltFilled], [ltEnd], [notWorked]) VALUES ('" +
                data.Date.ToShortDateString() + "'," + data.KmStart + "," + data.KmEnd + "," + data.KmDriven + "," +
                data.LtUsed + "," + data.LtStart + "," + data.LtFilled + "," + data.LtEnd + ", '" + data.NotWorked + "')";

            string SelectQuery = "SELECT [Date] FROM [Data] WHERE [Date] = '" + data.Date.ToShortDateString() + "'";

            SqlCeCommand cmd = null;
            cmd = new SqlCeCommand(SelectQuery, conn);
            var value = cmd.ExecuteScalar();
            cmd.CommandType = CommandType.Text;
            if(value == null)
            {
                cmd.CommandText = InsertQuery;
            }
            else
            {
                cmd.CommandText = UpdateQuery;
            }

            value = cmd.ExecuteNonQuery();
            return Convert.ToInt32(value) > 0;
        }

        public static Data SelectData(SqlCeConnection conn, DateTime date)
        {
            string SelectQuery = "SELECT * FROM [Data] WHERE [Date] = '" + date.Date.ToString() + "'";
            SqlCeCommand cmd = new SqlCeCommand(SelectQuery, conn);
            SqlCeDataReader dr = cmd.ExecuteReader();
            Data data = new Data();
            if (dr.Read())
            {
                data.Date = Convert.ToDateTime(dr["Date"].ToString());
                data.KmDriven = Convert.ToInt32(dr["kmDriven"].ToString());
                data.KmEnd = Convert.ToInt32(dr["kmEnd"].ToString());
                data.KmStart = Convert.ToInt32(dr["kmStart"].ToString());
                data.LtEnd = Convert.ToInt32(dr["ltEnd"].ToString());
                data.LtUsed = Convert.ToInt32(dr["ltUsed"].ToString());
                data.LtStart = Convert.ToInt32(dr["ltStart"].ToString());
                data.LtFilled = Convert.ToInt32(dr["ltFilled"].ToString());
                data.NotWorked = Convert.ToBoolean(dr["notWorked"]);
            }
            else
            {
                data = null;
            }
            return data;
        }
    }
}
